package idv.arthur.work;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

/**
 * Servlet implementation class DelMRitem
 */ 
public class QuerySquad extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public QuerySquad() {
        super();
        // TODO Auto-generated constructor stub
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    	doPost(request,response);
    }

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		request.setCharacterEncoding("UTF-8");
		
		Widgets wid = new Widgets();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String strSQL = "SELECT COUNT(*) FROM squad";
		ResultTableSet resultTableSet = new ResultTableSet();
		ArrayList<InfoStruc> infoStrucs = new ArrayList<InfoStruc>();
		
		String bss = "MBMS";

		boolean isForJqGrid = Boolean.parseBoolean(request.getParameter("isForJqGrid"));
		boolean isMasterDetail = request.getParameter("tarEnv")==null?false:true;
		boolean isForDepApply = (request.getParameter("getDataType") != null && request.getParameter("getDataType").equalsIgnoreCase("mapping"))?true:false;
		
		int page = Integer.parseInt(request.getParameter("page"));
		int total_pages = 1;
		int count  = 0;
		int limit = Integer.parseInt(request.getParameter("rows"));
		
		try {
			conn = wid.getConn(true);	
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			rs.next();
			count  = rs.getInt(1);
			
			if ( count  != 0 ) {
				
				if (limit  < count) {
					total_pages = (count / limit)+1;	
				}
				
				if (isForJqGrid) {
					strSQL = "SELECT subsysid,subsysname FROM subsys WHERE bssid=?";
					ps = conn.prepareStatement(strSQL);
					ps.setString(1, bss);
					rs = ps.executeQuery();
					
					while ( rs.next() ) {
						InfoStruc infoStruc = new InfoStruc();
						infoStruc.setId(rs.getString("subsysid"));
						infoStruc.setName(rs.getString("subsysname"));
						infoStrucs.add(infoStruc);
					}
				} else {
					if (isMasterDetail) {
						String tarSubSys = request.getParameter("tarSubSys");
						String tarEnv = request.getParameter("tarEnv");
						String tarSys = request.getParameter("tarSys");
								
						strSQL = "SELECT t1.path, t1.creator,t1.createdDateTime, t1.envid, t1.squadid,t1.subsysid,t1.squadname,t2.subsysname,t3.username FROM squad t1, subsys t2, personnel t3 WHERE t1.creator=t3.userid AND t1.subsysid=t2.subsysid AND t1.bssid=? AND t1.subsysid=? AND t1.envid=?";
						ps = conn.prepareStatement(strSQL);
						ps.setString(1, tarSys);
						ps.setString(2, tarSubSys);
						ps.setString(3, tarEnv);
					} else {
						String subSysId = request.getParameter("subSysId");
						strSQL = "SELECT t1.squadid,t1.envid,t1.squadname,t2.envname FROM squad t1, env t2,personnel t3 WHERE t1.envid=t2.envid AND t1.bssid=? AND t1.subsysid=?";
						ps = conn.prepareStatement(strSQL);
						ps.setString(1, bss);
						ps.setString(2, subSysId);
					}
					
					rs = ps.executeQuery();
					ArrayList<Rows> rows = new ArrayList<Rows>();
					
					while ( rs.next() ) {
						String squadid = rs.getString("squadid"); 
						Rows row = new Rows();
						row.setId(squadid);
						if (isForDepApply) {
							String[] cell = new String[6];
							cell[1] = squadid;
							cell[2] = rs.getString("squadname");
							if (isMasterDetail) {
								cell[3] = rs.getString("path");
								cell[4] = rs.getString("creator")+" ("+rs.getString("username")+")";
								cell[5] = wid.changeTimeFormat(rs.getTimestamp("createdDateTime"));
							} else {
								cell[2] = rs.getString("envname");
								cell[3] = rs.getString("envid");
							}
							row.setCell(cell);
							rows.add(row);
						} else {
							String[] cell = new String[5];
							cell[0] = squadid;
							cell[1] = rs.getString("squadname");
							if (isMasterDetail) {
								cell[2] = rs.getString("path");
								cell[3] = rs.getString("creator")+" ("+rs.getString("username")+")";
								cell[4] = wid.changeTimeFormat(rs.getTimestamp("createdDateTime"));
							} else {
								cell[2] = rs.getString("envname");
								cell[3] = rs.getString("envid");
							}
							row.setCell(cell);
							rows.add(row);
						}
					}
					
					resultTableSet.setRows(rows);
					resultTableSet.setPage(String.valueOf(page));
					resultTableSet.setTotal(total_pages);
					resultTableSet.setRecords(String.valueOf(count));
				}
			} else {
				page =0;
				total_pages=0;
				resultTableSet.setPage(String.valueOf(page));
				resultTableSet.setTotal(total_pages);
				resultTableSet.setRecords(String.valueOf(count));
			}
			
			
			
		} catch ( SQLException e) {
			System.out.println(wid.errProcess(e));
		} finally {
			try {
				if (rs != null) {rs.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				System.out.println(wid.errProcess(e));
			}
		}
		
		response.setContentType("application/json");
		response.setCharacterEncoding("UTF-8");
		if (isForJqGrid) {
			response.getWriter().write(new Gson().toJson(infoStrucs));
		} else {
			response.getWriter().write(new Gson().toJson(resultTableSet));	
		}
				
	}
}